﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
using System.Diagnostics;
using Microsoft.Office.Interop.Excel;

public partial class ThanhToanCongNoTra : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    string frmName = "frmTLCongNoTra";
    static int f_SuDung = 0, f_Them = 0, f_Sua = 0, f_Xoa = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static System.Data.DataTable dt;
    static int PageNumber = 5;
    static int CurrentPage = 1;
    double tongphaitravat;
    double tongphaitrakhongvat;
    int pc;
    int loaicongno;
    double sotien;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])))
            gstGetMess("Hết phiên làm việc, vui lòng đăng nhập để tiếp tục", "Default.aspx");
        else
        {
            if (!IsPostBack)
            {

                string idnguoidung = Session["IDUser"].ToString();
                string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

                System.Data.DataTable dt2 = ex.GetData_Text(sql);
                if (dt2.Rows.Count > 0)
                {
                    f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                    f_Them = int.Parse(dt2.Rows[0]["Them"].ToString());
                    f_Sua = int.Parse(dt2.Rows[0]["Sua"].ToString());
                    f_Xoa = int.Parse(dt2.Rows[0]["Xoa"].ToString());
                    f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                    f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
                }
                if (f_SuDung.Equals(1))
                {
                    Title = "Thiết lập công nợ trả vat | " + Header;

                    btnCancel_Click(sender, e);
                    int year1 = DateTime.Now.Year - 3;
                    int year2 = DateTime.Now.Year;
                    for (int i = year2; i >= year1; i--)
                    {
                        dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
                        drnam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
                    }
                    dlNam.SelectedValue = year2.ToString();
                    LoadNCCTK();
                    LoadTKKhongVAT();
                    LoadTK();
                    Button4.Visible = false;
                    Button5.Visible = false;

                }
                else
                    gstGetMess("Bạn không có quyền xem trang này", "Home.aspx");

            }
        }
    }
    protected void btnCancel_Click(object sender, EventArgs e)
    {
        LoadNhaCungCap();
        txtsotienthanhtoankynayVAT.Text = "0";
        txtsotienthanhtoankynaykhongVAT.Text = "0";
        drtkkhongvat.Enabled = false;

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List", p);
        GV.DataBind();
        LoadFooterGV();
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GVPhieuChi.DataSource = ex.GetData("sp_xdaphieuchicongno_List", p);
        GVPhieuChi.DataBind();
        LoadFooterGVPhieuChi();

        btncapnhat.Visible = false;
        btnSave.Visible = true;
        txtngaythanhtoan.Text = "";
        drhinhthuckhongvat.SelectedIndex = 0;
        drhinhthucthanhtoanVAT.SelectedIndex = 0;
        drtaikhoan.SelectedIndex = 0;
        drtkkhongvat.SelectedIndex = 0;

        btnFirst.Visible = true;
        btnNext.Visible = true;
        btnPre.Visible = true;

        Button1.Visible = true;
        Button2.Visible = true;
        Button3.Visible = true;
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    void LoadNhaCungCap()
    {
        drnhacungcap.Items.Clear();
        drnhacungcap.DataSource = ex.GetData("sp_ThanhToanCN_NhaCungCap_ListAll");
        drnhacungcap.DataBind();
        drnhacungcap.Items.Insert(0, new ListItem("--Chọn bên thanh toán--", ""));
        drnhacungcap.SelectedIndex = 0;
    }
    void LoadTK()
    {
        drtaikhoan.Items.Clear();
        drtaikhoan.DataSource = ex.GetData("sp_ListTaiKhoan_KhongVAT");
        drtaikhoan.DataBind();
        drtaikhoan.Items.Insert(0, new ListItem("--Chọn tài khoản chuyển tiền--", ""));
        drtaikhoan.SelectedIndex = 0;
    }
    void LoadTKKhongVAT()
    {
        drtkkhongvat.Items.Clear();
        drtkkhongvat.DataSource = ex.GetData("sp_ListTaiKhoan_KhongVAT");
        drtkkhongvat.DataBind();
        drtkkhongvat.Items.Insert(0, new ListItem("--Chọn tài khoản chuyển tiền không VAT--", ""));
        drtkkhongvat.SelectedIndex = 0;
    }
    void LoadNCCTK()
    {
        dlnhacungcap.Items.Clear();
        dlnhacungcap.DataSource = ex.GetData("sp_ThanhToanCN_NhaCungCap_TimKiem");
        dlnhacungcap.DataBind();
        dlnhacungcap.Items.Insert(0, new ListItem("--Chọn bên thanh toán--", ""));
        dlnhacungcap.SelectedIndex = 0;
    }

    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drnhacungcap.SelectedIndex == 0)
        {
            drnhacungcap.Items.Clear();
            drnhacungcap.DataSource = ex.GetData("sp_ThanhToanCN_NhaCungCap");
            drnhacungcap.DataBind();
            drnhacungcap.Items.Insert(0, new ListItem("--Chọn nhà cung cấp--", ""));
            drnhacungcap.SelectedIndex = 0;
            drnhacungcap_SelectedIndexChanged(sender, e);

            txttongthanhtoankynay.Text = "0";
            txtsoduconlaikhongVAT.Text = "0";
            txtsoduconlaiVAT.Text = "0";
            txttongduconlai.Text = "0";
        }
        else
            drnhacungcap_SelectedIndexChanged(sender, e);
    }
    protected void drnhacungcap_SelectedIndexChanged(object sender, EventArgs e)
    {
        #region thanhtienVAT
        p = new SqlParameter[3];
        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_TienHangThangNay_CongNo", p);
        if (dt.Rows[0]["gia"].ToString().Equals(""))
            txttienhangVAT.Text = "0";
        else
            txttienhangVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["gia"].ToString()));

        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_TienCuocVCThangNay_CongNo", p);
        if (dt.Rows[0]["giacuoc"].ToString().Equals(""))
            txttiencuocVAT.Text = "0";
        else
            txttiencuocVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["giacuoc"].ToString()));
        double thanhtienVAT;
        thanhtienVAT = double.Parse(txttiencuocVAT.Text) + double.Parse(txttienhangVAT.Text);
        txtthanhtienkynayVAT.Text = ConvertDecimal(thanhtienVAT);
        #endregion
        #region sodukitruocVAT

        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToan_SoDuKiTruoc", p);
        txtsodukitruocVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sodukitruoc"].ToString()));
        #endregion
        double tongtienVAt;
        tongtienVAt = thanhtienVAT + double.Parse(txtsodukitruocVAT.Text);
        txttongduVAT.Text = ConvertDecimal(double.Parse(tongtienVAt.ToString()));


        #region thanhtienkhongVAT
        p = new SqlParameter[3];
        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_TienHangThangNay_CongNo_KhongVAT", p);
        if (dt.Rows[0]["gia"].ToString().Equals(""))
            txttienhangkhongVAT.Text = "0";
        else
            txttienhangkhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["gia"].ToString()));

        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_TienCuocVCKhongVATThangNay_CongNo", p);
        if (dt.Rows[0]["giacuoc"].ToString().Equals(""))
            txttiencuockhongVAT.Text = "0";
        else
            txttiencuockhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["giacuoc"].ToString()));

        double thanhtienkhongVAT;
        thanhtienkhongVAT = double.Parse(txttiencuockhongVAT.Text) + double.Parse(txttienhangkhongVAT.Text);
        txtthanhtienkhongVAT.Text = ConvertDecimal(double.Parse(thanhtienkhongVAT.ToString()));

        #endregion

        #region
        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("sp_ThanhToan_SoDuKiTruocKhongVAT_CongNoTra", p);
        txtsodukitruockhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sodukitruoc"].ToString()));
        #endregion

        double tongtienkhongVAt;
        tongtienkhongVAt = thanhtienkhongVAT + double.Parse(txtsodukitruockhongVAT.Text);
        txttongdukhongVAT.Text = ConvertDecimal(tongtienkhongVAt);
        #region sotiendathanhtoankhongvat
        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("f_sp_SoTienThanhToanKyNay_CongNoTraKhongVAT", p);
        txtsotienthanhtoankynaykhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sotiendathanhtoan"].ToString()));
        #endregion
        #region thanhtoankynay

        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("f_sp_SoTienThanhToanKyNay_CongNoTraVAT", p);
        txtsotienthanhtoankynayVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sotiendathanhtoan"].ToString()));
        #endregion
        #region thanhtoankytruoc VAT

        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("f_sp_SoTienDaThanhToanKyTruoc_CongNoVAT", p);
        txtsotiendathanhtoanVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sotiendathanhtoan"].ToString()));
        txtthcovat.Text = ConvertDecimal(double.Parse(txtsotiendathanhtoanVAT.Text) + tongtienVAt);
        #endregion
        #region thanhtoankykhongtruoc VAT

        p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        p[1] = new SqlParameter("@Thang", dlThang.SelectedValue);
        p[1].SqlDbType = SqlDbType.Int;
        p[2] = new SqlParameter("@Nam", dlNam.SelectedValue);
        p[2].SqlDbType = SqlDbType.Int;
        dt = ex.GetData("f_sp_SoTienDaThanhToanKyTruoc_CongNoTraKhongVAT", p);
        txtsotiendathanhtoankhongVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sotiendathanhtoan"].ToString()));
        TextBox1.Text = ConvertDecimal(double.Parse(txtsotiendathanhtoankhongVAT.Text) + tongtienkhongVAt);
        TextBox2.Text = ConvertDecimal(double.Parse(TextBox1.Text) + double.Parse(txtthcovat.Text));
        #endregion
        double ttkt;
        ttkt = double.Parse(txtsotiendathanhtoanVAT.Text) + double.Parse(txtsotiendathanhtoankhongVAT.Text);
        txttongthanhtoankytruoc.Text = ConvertDecimal(ttkt);

        tongphaitravat = double.Parse(txttongduVAT.Text);
        txttongphaitraVAT.Text = ConvertDecimal(tongphaitravat);

        tongphaitrakhongvat = double.Parse(txttongdukhongVAT.Text);
        txttongphaitrakhongVAT.Text = ConvertDecimal(tongphaitrakhongvat);

        double tongthanhtoan;
        tongthanhtoan = double.Parse(txtsotienthanhtoankynaykhongVAT.Text) + double.Parse(txtsotienthanhtoankynayVAT.Text);
        txttongthanhtoankynay.Text = ConvertDecimal(tongthanhtoan);
        double soduconlaivat;
        soduconlaivat = tongphaitravat - double.Parse(txtsotienthanhtoankynayVAT.Text);
        txtsoduconlaiVAT.Text = ConvertDecimal(soduconlaivat);
        double soduconlaikhongvat;
        soduconlaikhongvat = tongphaitrakhongvat - double.Parse(txtsotienthanhtoankynaykhongVAT.Text);
        txtsoduconlaikhongVAT.Text = ConvertDecimal(soduconlaikhongvat);
        double tongduconlai;
        tongduconlai = soduconlaikhongvat + soduconlaivat;
        txttongduconlai.Text = ConvertDecimal(tongduconlai);

        drhinhthuckhongvat.SelectedIndex = 0;
        drhinhthucthanhtoanVAT.SelectedIndex = 0;
        double tongsodutruoc;
        tongsodutruoc = double.Parse(txtsodukitruockhongVAT.Text.Trim()) + double.Parse(txtsodukitruocVAT.Text.Trim());
        txtsodukytruoc.Text = ConvertDecimal(tongsodutruoc);
        //#region lenhchi
        //if (txtngaythanhtoan.Text!="")
        //{
        //    p = new SqlParameter[2];
        //    p[0] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
        //    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
        //    p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
        //    p[1].SqlDbType = SqlDbType.SmallDateTime;

        //    dt = ex.GetData("sp_xdathanhtoancongnotra_Lenhchi_List", p);
        //    if (dt.Rows.Count > 0)
        //    {
        //        txtsotienthanhtoankynayVAT.Text = ConvertDecimal(double.Parse(dt.Rows[0]["sotien"].ToString()));
        //        drtaikhoan.SelectedValue = dt.Rows[0]["taikhoan"].ToString();
        //        txtnoidung.Text = dt.Rows[0]["noidung"].ToString();
        //    }
        //    else
        //    {
        //        txtsotienthanhtoankynayVAT.Text = "0";
        //        drtaikhoan.SelectedIndex = 0;
        //        txtnoidung.Text = "";
        //    }
        //}
        //#endregion
    }
    protected void txtsotienthanhtoan_TextChanged(object sender, EventArgs e)
    {
        //double thanhtienkynay;
        //thanhtienkynay = double.Parse(txtthanhtienkynay.Text);

        //double sotienthanhtoan;
        //sotienthanhtoan = double.Parse(txtsotienthanhtoan.Text); ;
        //double sotienconlai;
        //sotienconlai = thanhtienkynay + double.Parse(txtsodukitruoc.Text) - sotienthanhtoan - double.Parse(txtsotiendathanhtoan.Text);
        //txtsotienconlai.Text = sotienconlai.ToString();
    }
    protected void drhinhthucthanhtoan_SelectedIndexChanged(object sender, EventArgs e)
    {

    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (f_Them.Equals(1))
        {

            try
            {
                loaicongno = 2;
                p = new SqlParameter[1];
                p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                dt = ex.GetData("sp_xdagetsophieuchiconnotravat_TienMat", p);
                int sopc = Int16.Parse(dt.Rows[0]["sopc"].ToString());
                if (sopc == 0)
                    pc = 1;
                else pc = sopc + 1;
                Guid idtt = Guid.NewGuid();
                p = new SqlParameter[21];
                p[0] = new SqlParameter("@IDs", idtt);
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                p[1] = new SqlParameter("@NhaCungCap", ex.GetGuid(drnhacungcap.SelectedValue));
                p[1].SqlDbType = SqlDbType.UniqueIdentifier;
                p[2] = new SqlParameter("@SoDuKiTruocVAT", txtsodukitruocVAT.Text.Trim().Replace(",", ""));
                p[2].SqlDbType = SqlDbType.Float;
                p[3] = new SqlParameter("@TienHangVAT", txttienhangVAT.Text.Trim().Replace(",", ""));
                p[3].SqlDbType = SqlDbType.Float;
                p[4] = new SqlParameter("@TienCuocVAT", txttiencuocVAT.Text.Trim().Replace(",", ""));
                p[4].SqlDbType = SqlDbType.Float;
                p[5] = new SqlParameter("@SoDuKiTruocKhongVAT", txtsodukitruockhongVAT.Text.Trim().Replace(",", ""));
                p[5].SqlDbType = SqlDbType.Float;
                p[6] = new SqlParameter("@TienHangKhongVAT", txttienhangkhongVAT.Text.Trim().Replace(",", ""));
                p[6].SqlDbType = SqlDbType.Float;
                p[7] = new SqlParameter("@TienCuocKhongVAT", txttiencuockhongVAT.Text.Trim().Replace(",", ""));
                p[7].SqlDbType = SqlDbType.Float;
                p[8] = new SqlParameter("@SoTienThanhToanTruocVAT", txtsotiendathanhtoanVAT.Text.Trim().Replace(",", ""));
                p[8].SqlDbType = SqlDbType.Float;
                p[9] = new SqlParameter("@SoTienThanhToanKhongVAT", txtsotiendathanhtoankhongVAT.Text.Trim().Replace(",", ""));
                p[9].SqlDbType = SqlDbType.Float;
                p[10] = new SqlParameter("@SoTienThanhToanKiNayVAT", double.Parse(TextBox3.Text.Trim().Replace(",", "").Equals("") ? "0" : TextBox3.Text.Trim()));
                p[10].SqlDbType = SqlDbType.Float;
                p[11] = new SqlParameter("@SoTienThanhToanKiNayKhongVAT", double.Parse(TextBox4.Text.Trim().Replace(",", "").Equals("") ? "0" : TextBox4.Text.Trim()));
                p[11].SqlDbType = SqlDbType.Float;
                p[12] = new SqlParameter("@HinhThucThanhToanVAT", 2);
                p[12].SqlDbType = SqlDbType.Int;
                p[13] = new SqlParameter("@HinhThucThanhToanKhongVAT", drhinhthuckhongvat.SelectedValue);
                p[13].SqlDbType = SqlDbType.Int;
                p[14] = new SqlParameter("@SoPC", pc);
                p[14].SqlDbType = SqlDbType.Int;
                p[15] = new SqlParameter("@TaiKhoanKhongVAT", ex.GetGuid(drtkkhongvat.SelectedValue));
                p[15].SqlDbType = SqlDbType.UniqueIdentifier;
                p[16] = new SqlParameter("@TaiKhoanVAT", ex.GetGuid(drtaikhoan.SelectedValue));
                p[16].SqlDbType = SqlDbType.UniqueIdentifier;
                p[17] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
                p[17].SqlDbType = SqlDbType.SmallDateTime;
                p[18] = new SqlParameter("@Del", 1);
                p[18].SqlDbType = SqlDbType.Int;
                p[19] = new SqlParameter("@NoiDung", txtnoidung.Text.Trim());
                p[19].SqlDbType = SqlDbType.NVarChar;
                p[20] = new SqlParameter("@IDLenhChi", DBNull.Value);
                p[20].SqlDbType = SqlDbType.UniqueIdentifier;
                ex.ExecuteNonQuerry("sp_XDAThanhToanCongNoTra_Add", p);
                if (double.Parse(TextBox3.Text.Trim().Replace(",", "").Equals("") ? "0" : TextBox3.Text.Trim())==0)
                    sotien = double.Parse(TextBox4.Text.Trim().Replace(",", ""));
                else
                    sotien = double.Parse(TextBox3.Text.Trim().Replace(",", ""));

                p = new SqlParameter[11];
                p[0] = new SqlParameter("@IDs", Guid.NewGuid());
                p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                p[1] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@NoiDung", txtnoidung.Text.Trim());
                p[2].SqlDbType = SqlDbType.NVarChar;
                p[3] = new SqlParameter("@DonVi", ex.GetGuid(drnhacungcap.SelectedValue));
                p[3].SqlDbType = SqlDbType.UniqueIdentifier;
                p[4] = new SqlParameter("@SoPC", pc);
                p[4].SqlDbType = SqlDbType.Int;
                p[5] = new SqlParameter("@TaiKhoan", ex.GetGuid(drtaikhoan.SelectedValue));
                p[5].SqlDbType = SqlDbType.UniqueIdentifier;
                p[6] = new SqlParameter("@Del", 1);
                p[6].SqlDbType = SqlDbType.Int;
                p[7] = new SqlParameter("@LoaiCongNo", loaicongno);
                p[7].SqlDbType = SqlDbType.Int;
                p[8] = new SqlParameter("@SoTien", sotien);
                p[8].SqlDbType = SqlDbType.Float;
                p[9] = new SqlParameter("@IDLenhChi", idtt);
                p[9].SqlDbType = SqlDbType.UniqueIdentifier;
                p[10] = new SqlParameter("@NhanVien", DBNull.Value);
                p[10].SqlDbType = SqlDbType.UniqueIdentifier;
                ex.ExecuteNonQuerry("sp_xdaphieuchicongno_Add", p);


                #region laylaitudau
                TextBox3.Text = "0";
                TextBox4.Text = "0";
                drtkkhongvat.Enabled = false;

                p = new SqlParameter[2];
                p[0] = new SqlParameter("@PageNumber", CurrentPage);
                p[0].SqlDbType = SqlDbType.Int;
                p[1] = new SqlParameter("@RowspPage", PageNumber);
                p[1].SqlDbType = SqlDbType.Int;

                GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List", p);
                GV.DataBind();
                LoadFooterGV();
                p = new SqlParameter[2];
                p[0] = new SqlParameter("@PageNumber", CurrentPage);
                p[0].SqlDbType = SqlDbType.Int;
                p[1] = new SqlParameter("@RowspPage", PageNumber);
                p[1].SqlDbType = SqlDbType.Int;

                GVPhieuChi.DataSource = ex.GetData("sp_xdaphieuchicongno_List", p);
                GVPhieuChi.DataBind();
                btncapnhat.Visible = false;
                btnSave.Visible = true;
                txtngaythanhtoan.Text = "";
                drhinhthuckhongvat.SelectedIndex = 0;
                drhinhthucthanhtoanVAT.SelectedIndex = 0;
                drtaikhoan.SelectedIndex = 0;
                drtkkhongvat.SelectedIndex = 0;
                #endregion

                gstGetMess("Lưu thành công", "");
                drnhacungcap_SelectedIndexChanged(sender, e);
                LoadFooterGVPhieuChi();
            }
            catch (Exception ax)
            {
                gstGetMess("Có lỗi trong quá trình lưu, xem trong file log", "");
                ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tthanh toán công nợ trả\t" + ax.Message);
            }
        }
        else
            gstGetMess("Bạn không có quyền thêm dữ liệu trên trang này", "");
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List", p);
        GV.DataBind();
        LoadFooterGV();


        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            p = new SqlParameter[2];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;

            GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List", p);
            GV.DataBind();

            LoadFooterGV();

        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List", p);
        GV.DataBind();
        LoadFooterGV();

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (dlnhacungcap.SelectedIndex == 0 && dhinhthuc.SelectedIndex == 0)
        {
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@Thang", int.Parse(drthang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(drnam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;

            GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List_TimKiemKhongNhaCungCap", p);
            GV.DataBind();
            LoadFooterGV();
        }
        else if (dlnhacungcap.SelectedIndex > 0 && dhinhthuc.SelectedIndex > 0)
        {
            p = new SqlParameter[4];
            p[0] = new SqlParameter("@Thang", int.Parse(drthang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(drnam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@NCC", ex.GetGuid(dlnhacungcap.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            p[3] = new SqlParameter("@HinhThuc", dhinhthuc.SelectedValue);
            p[3].SqlDbType = SqlDbType.Int;
            GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List_TimKiem_HinhThuc_NCC", p);
            GV.DataBind();
            LoadFooterGV();
        }
        else if (dhinhthuc.SelectedIndex == 0 && dlnhacungcap.SelectedIndex > 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@Thang", int.Parse(drthang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(drnam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@NCC", ex.GetGuid(dlnhacungcap.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List_TimKiem", p);
            GV.DataBind();
            LoadFooterGV();
        }

        else if (dhinhthuc.SelectedIndex > 0 && dlnhacungcap.SelectedIndex == 0)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@Thang", int.Parse(drthang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(drnam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@HinhThuc", dhinhthuc.SelectedValue);
            p[2].SqlDbType = SqlDbType.Int;
            GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List_TimKiem_HinhThuc", p);
            GV.DataBind();
            LoadFooterGV();
        }
        Button4.Visible = GV.Rows.Count > 0;
        btnFirst.Visible = false;
        btnNext.Visible = false;
        btnPre.Visible = false;



    }
    protected void txtsotienthanhtoankynayVAT_TextChanged(object sender, EventArgs e)
    {
        double soduvat;
        double tong;
        soduvat = double.Parse(txttongphaitraVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txttongphaitraVAT.Text.Trim().Replace(",", "")) - double.Parse(txtsotienthanhtoankynayVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txtsotienthanhtoankynayVAT.Text.Trim().Replace(",", ""));
        txtsoduconlaiVAT.Text = ConvertDecimal(soduvat);
        tong = double.Parse(txtsotienthanhtoankynayVAT.Text) + double.Parse(txtsotienthanhtoankynaykhongVAT.Text);
        txttongthanhtoankynay.Text = ConvertDecimal(tong);
        double tongconlai;
        tongconlai = double.Parse(txtsoduconlaikhongVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txtsoduconlaikhongVAT.Text.Trim().Replace(",", "")) + double.Parse(txtsoduconlaiVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txtsoduconlaiVAT.Text.Trim().Replace(",", ""));
        txttongduconlai.Text = ConvertDecimal(tongconlai);
    }
    protected void txtsotienthanhtoankynaykhongVAT_TextChanged(object sender, EventArgs e)
    {
        double soduvat;
        double tong;
        soduvat = double.Parse(txttongphaitrakhongVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txttongphaitrakhongVAT.Text.Trim().Replace(",", "")) - double.Parse(txtsotienthanhtoankynaykhongVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txtsotienthanhtoankynaykhongVAT.Text.Trim().Replace(",", ""));
        txtsoduconlaikhongVAT.Text = ConvertDecimal(soduvat);
        tong = double.Parse(txtsotienthanhtoankynayVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txtsotienthanhtoankynayVAT.Text.Trim().Replace(",", "")) + double.Parse(txtsotienthanhtoankynaykhongVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txtsotienthanhtoankynaykhongVAT.Text.Trim().Replace(",", ""));
        txttongthanhtoankynay.Text = ConvertDecimal(tong);
        double tongconlai;
        tongconlai = double.Parse(txtsoduconlaikhongVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txtsoduconlaikhongVAT.Text.Trim().Replace(",", "")) + double.Parse(txtsoduconlaiVAT.Text.Trim().Replace(",", "").Equals("") ? "0" : txtsoduconlaiVAT.Text.Trim().Replace(",", ""));
        txttongduconlai.Text = ConvertDecimal(tongconlai);
    }
    string ConvertDecimal(double s)
    {
        string q = "";
        if (s.ToString().Contains("."))
        {
            double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
            q = (x != 0 && x > 999) ? string.Format("{0:0,0}", x) : x.ToString();
            q += s.ToString().Substring(s.ToString().IndexOf('.'));
        }
        else
            q = (s != 0 && s > 999) ? string.Format("{0:0,0}", s) : s.ToString();
        return q;
    }
    protected void drhinhthuckhongvat_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (drhinhthuckhongvat.SelectedValue == "1")
            drtkkhongvat.Enabled = true;
        else
            drtkkhongvat.Enabled = false;
    }
    protected void GV_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        string ID = e.CommandArgument.ToString();
        hdID.Value = ID;
        Guid gID = new Guid(ID);

        if (e.CommandName.Equals("Del"))
        {

            if (f_Xoa.Equals(1))
            {
                try
                {
                    p = new SqlParameter[1];
                    p[0] = new SqlParameter("@IDs", gID);
                    p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                    dt = ex.GetData("sp_ThanhToanCongNoTra_ListHinhThuc", p);
                    int hinhthuc = Int16.Parse(dt.Rows[0]["hinhthucthanhtoanvat"].ToString());
                    if (hinhthuc == 2)
                    {
                        p = new SqlParameter[1];
                        p[0] = new SqlParameter("@IDs", gID);
                        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                        ex.ExecuteNonQuerry("sp_xdaThanhToanCongNoTra_DelTienMat", p);

                        p = new SqlParameter[1];
                        p[0] = new SqlParameter("@IDs", gID);
                        p[0].SqlDbType = SqlDbType.UniqueIdentifier;
                        ex.ExecuteNonQuerry("sp_xdaThanhToanCongNoTra_DelPhieuChiCongNo", p);
                        btnCancel_Click(sender, e);
                        gstGetMess("Xóa thành công", "");
                    }
                    else
                    {
                        gstGetMess("Không được quyền xóa", "");
                    }
                }
                catch (Exception ax)
                {
                    gstGetMess("Có lỗi trong quá trình xóa, xem trong file log", "");
                    ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tThanh toán công nợ\t" + ax.Message);

                }
            }
            else
                gstGetMess("Bạn không có quyền xóa dữ liệu trên trang này", "");
        }

        else
        {

            p = new SqlParameter[1];
            p[0] = new SqlParameter("@IDs", gID);
            p[0].SqlDbType = SqlDbType.UniqueIdentifier;
            dt = ex.GetData("[sp_ThanhToanCongNoTra_ListByID]", p);
            if (dt.Rows.Count > 0)
            {
                try
                {
                    string idlenhchi = dt.Rows[0]["idlenhchi"].ToString();
                    if (idlenhchi == "")
                    {
                        TextBox4.Text = dt.Rows[0]["sotienthanhtoankinaykhongvat"].ToString();
                        TextBox3.Text = dt.Rows[0]["sotienthanhtoankinayvat"].ToString();
                        txtnoidung.Text = dt.Rows[0]["noidung"].ToString();
                        dlThang.SelectedValue = dt.Rows[0]["thang"].ToString();
                        txtngaythanhtoan.Text = dt.Rows[0]["ngaythang"].ToString();
                        drnhacungcap.SelectedValue = dt.Rows[0]["nhacungcap"].ToString();
                        drnhacungcap_SelectedIndexChanged(sender, e);
                    }
                    else
                    {
                        gstGetMess("Không thể sửa đối với hình thức trả ngân hàng","");
                    }


                }
                catch (Exception ax)
                {
                    gstGetMess("Có lỗi trong quá trình xem, xem trong file log", "");
                    ex.WriteLog(Server.MapPath("Log.txt"), DateTime.Now.ToString() + "\tCông nợ trả\t" + ax.Message);
                }

            }
            btncapnhat.Visible = true;
            btnSave.Visible = false;
        }
    }
    protected void btncapnhat_Click(object sender, EventArgs e)
    {
        if (f_Sua.Equals(1))
        {
            Guid gID = new Guid(hdID.Value);
            p = new SqlParameter[5];
            p[0] = new SqlParameter("@IDs", gID);
            p[0].SqlDbType = SqlDbType.UniqueIdentifier;
            p[1] = new SqlParameter("@SoTienTTVAT", double.Parse(TextBox3.Text.Trim().Replace(",", "").Equals("") ? "0" : TextBox3.Text.Trim().Replace(",", "")));
            p[1].SqlDbType = SqlDbType.Float;
            p[2] = new SqlParameter("@SoTienTTKhongVAT", double.Parse(TextBox4.Text.Trim().Replace(",", "").Equals("") ? "0" : TextBox4.Text.Trim().Replace(",", "")));
            p[2].SqlDbType = SqlDbType.Float;
           
            p[3] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
            p[3].SqlDbType = SqlDbType.SmallDateTime;
            p[4] = new SqlParameter("@NoiDung", txtnoidung.Text.Trim());
            p[4].SqlDbType = SqlDbType.NVarChar;
            ex.ExecuteNonQuerry("sp_ThanhToanCongNoTra_Update", p);


            p = new SqlParameter[4];

            p[0] = new SqlParameter("@NgayThang", ex.GetNgayThang(txtngaythanhtoan.Text.Trim()));
            p[0].SqlDbType = SqlDbType.SmallDateTime;
            p[1] = new SqlParameter("@NoiDung", txtnoidung.Text.Trim());
            p[1].SqlDbType = SqlDbType.NVarChar;
           
            string txt = TextBox3.Text.Trim().Replace(",", "").Equals("") ? "0" : TextBox3.Text.Trim().Replace(",", "");
            if (double.Parse(txt).Equals(0))
            {

                sotien = double.Parse(TextBox4.Text.Trim().Replace(",", ""));
            }
            else
            {

                sotien = double.Parse(TextBox3.Text.Trim().Replace(",", ""));
            }
            p[2] = new SqlParameter("@SoTien", sotien);
            p[2].SqlDbType = SqlDbType.Float;
            p[3] = new SqlParameter("@IDLenhChi", gID);
            p[3].SqlDbType = SqlDbType.UniqueIdentifier;
            
            ex.ExecuteNonQuerry("sp_ThanhToanCongNoTra_UpdatePhieuChiCongNo", p);
            #region laylaitudau
            TextBox3.Text = "0";
            TextBox4.Text = "0";
            drtkkhongvat.Enabled = false;

            p = new SqlParameter[2];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;

            GV.DataSource = ex.GetData("sp_xdathanhtoancongnotra_List", p);
            GV.DataBind();
            LoadFooterGV();
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;

            GVPhieuChi.DataSource = ex.GetData("sp_xdaphieuchicongno_List", p);
            GVPhieuChi.DataBind();
            btncapnhat.Visible = false;
            btnSave.Visible = true;
            txtngaythanhtoan.Text = "";
            drhinhthuckhongvat.SelectedIndex = 0;
            drhinhthucthanhtoanVAT.SelectedIndex = 0;
            drtaikhoan.SelectedIndex = 0;
            drtkkhongvat.SelectedIndex = 0;
            #endregion

            gstGetMess("Cập thành công", "");
            drnhacungcap_SelectedIndexChanged(sender, e);
            LoadFooterGVPhieuChi();
        }
        else
            gstGetMess("Bạn không có quyền sửa dữ liệu trên trang này", "");

    }
    void LoadFooterGV()
    {
        double ttiencovat = 0, sonhienlieu = 0;
        for (int i = 0; i < GV.Rows.Count; i++)
        {

            sonhienlieu += double.Parse(GV.Rows[i].Cells[4].Text.Replace(",", ""));
            ttiencovat += double.Parse(GV.Rows[i].Cells[5].Text.Replace(",", ""));

        }

        if (GV.Rows.Count > 0)
        {
            GV.FooterRow.Cells[2].Text = "Tổng số";
            GV.FooterRow.Cells[2].HorizontalAlign = HorizontalAlign.Center;
            //GV.FooterRow.Cells[4].HorizontalAlign = HorizontalAlign.Center;
            GV.FooterRow.Cells[4].Text = sonhienlieu > 999 ? string.Format("{0:0,0}", sonhienlieu) : sonhienlieu.ToString();
            GV.FooterRow.Cells[5].Text = ttiencovat > 999 ? string.Format("{0:0,0}", ttiencovat) : ttiencovat.ToString();


        }
    }

    void LoadFooterGVPhieuChi()
    {
        double sonhienlieu = 0;
        for (int i = 0; i < GVPhieuChi.Rows.Count; i++)
        {

            sonhienlieu += double.Parse(GVPhieuChi.Rows[i].Cells[3].Text.Replace(",", ""));


        }

        if (GVPhieuChi.Rows.Count > 0)
        {
            GVPhieuChi.FooterRow.Cells[1].Text = "Tổng số";
            GVPhieuChi.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Center;
            GVPhieuChi.FooterRow.Cells[3].Text = sonhienlieu > 999 ? string.Format("{0:0,0}", sonhienlieu) : sonhienlieu.ToString();



        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GVPhieuChi.DataSource = ex.GetData("sp_xdaphieuchicongno_List", p);
        GVPhieuChi.DataBind();
        LoadFooterGVPhieuChi();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            p = new SqlParameter[2];
            p[0] = new SqlParameter("@PageNumber", CurrentPage);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@RowspPage", PageNumber);
            p[1].SqlDbType = SqlDbType.Int;

            GVPhieuChi.DataSource = ex.GetData("sp_xdaphieuchicongno_List", p);
            GVPhieuChi.DataBind();
            LoadFooterGVPhieuChi();


        }
        else
        {
            Button1.Enabled = false;
            Button2.Enabled = false;
            Button3.Enabled = true;
        }
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        CurrentPage++;
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@PageNumber", CurrentPage);
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@RowspPage", PageNumber);
        p[1].SqlDbType = SqlDbType.Int;

        GVPhieuChi.DataSource = ex.GetData("sp_xdaphieuchicongno_List", p);
        GVPhieuChi.DataBind();

        LoadFooterGVPhieuChi();
        if (GVPhieuChi.Rows.Count.Equals(0))
        {
            Button2.Enabled = true;
            Button3.Enabled = false;
        }
        else
        {
            Button2.Enabled = true;
            Button3.Enabled = true;
        }
        Button1.Enabled = true;
    }
    protected void btnXemPC_Click(object sender, EventArgs e)
    {
        if (dlnhacungcap.SelectedIndex == 0)
        {
            p = new SqlParameter[2];
            p[0] = new SqlParameter("@Thang", int.Parse(drthang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(drnam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;

            GVPhieuChi.DataSource = ex.GetData("sp_xdaphieuchicongno_ListTK_TheoThang", p);
            GVPhieuChi.DataBind();
            LoadFooterGVPhieuChi();
        }
        else
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@Thang", int.Parse(drthang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(drnam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@NCC", ex.GetGuid(dlnhacungcap.SelectedValue));
            p[2].SqlDbType = SqlDbType.UniqueIdentifier;
            GVPhieuChi.DataSource = ex.GetData("sp_xdaphieuchicongno_ListTK_TheoThang_NCC", p);
            GVPhieuChi.DataBind();

        }
        Button5.Visible = GV.Rows.Count > 0;
        Button1.Visible = false;
        Button2.Visible = false;
        Button3.Visible = false;
    }
    protected void txtngaythanhtoan_TextChanged(object sender, EventArgs e)
    {
        drnhacungcap_SelectedIndexChanged(sender, e);
    }
    protected void GV_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            GridView HeaderGrid = (GridView)sender;
            GridViewRow HeaderGridRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
            HeaderGridRow.Height = 20;
            HeaderGridRow.HorizontalAlign = HorizontalAlign.Center;
            TableCell HeaderCell = new TableCell();

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "Số tiền thanh toán";
            HeaderCell.ColumnSpan = 2;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;



            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;


            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;



            GV.Controls[0].Controls.AddAt(0, HeaderGridRow);
        }
    }
    protected void Button4_Click(object sender, EventArgs e)
    {
        try
        {
            if (GV.Rows.Count > 0)
            {
                Process[] processBefore = Process.GetProcessesByName("excel");
                Microsoft.Office.Interop.Excel.ApplicationClass exc = new Microsoft.Office.Interop.Excel.ApplicationClass();
                object missvalues = System.Reflection.Missing.Value;
                string checkpath = Server.MapPath("Excel/DSThanhToan.xls");
                Microsoft.Office.Interop.Excel.Workbook wb = exc.Workbooks.Open(checkpath, 0, true, 3, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                exc.Visible = false;
                exc.StandardFont = "Times New Roman";
                exc.StandardFontSize = 13;

                string bcc = dlnhacungcap.SelectedIndex.Equals(0) ? "" : dlnhacungcap.SelectedItem.Text;
                string thang = drthang.SelectedItem.Text + " " + drnam.SelectedItem.Text;

                Process[] processAffre = Process.GetProcessesByName("excel");
                int processID = 0;
                foreach (Process pro in processAffre)
                {
                    if (!processBefore.Select(p => p.Id).Contains(pro.Id))
                        processID = pro.Id;
                }


                int stt = GV.Rows.Count;
                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

                ws.Name = dlnhacungcap.SelectedIndex > 0 ? dlnhacungcap.SelectedItem.Text : "Bảng tổng hợp";
                Microsoft.Office.Interop.Excel.Range Border;

                #region TongHop
                string tieudethang1 = drthang.SelectedItem.Text.ToUpper() + " " + drnam.SelectedItem.Text.ToUpper();

                ws.get_Range("A3", "H3").Merge(true);
                Microsoft.Office.Interop.Excel.Range a4 = ws.get_Range("A3", "H3");
                a4.FormulaR1C1 = "DANH SÁCH THANH TOÁN CÔNG NỢ " + tieudethang1;
                a4.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                a4.Font.Size = 18;
                a4.EntireColumn.RowHeight = 23;
                a4.Font.Name = "Times New Roman";
                a4.Font.Bold = true;

                ws.get_Range("A4", "H4").Merge(true);
                Microsoft.Office.Interop.Excel.Range a4m42 = ws.get_Range("A4", "H4");
                a4m42.FormulaR1C1 = dlnhacungcap.SelectedIndex > 0 ? "Bên nhận tiền: " + dlnhacungcap.SelectedItem.Text : "";
                a4m42.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                a4m42.Font.Size = 14;
                a4m42.EntireColumn.RowHeight = 23;
                a4m42.Font.Name = "Times New Roman";
                a4m42.Font.Bold = true;

                int si = 7;
                for (int i = 0; i < stt; i++)
                {
                    Border = ws.get_Range("A" + (si), "H" + (si + i));
                    Border.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
                    #region Cells
                    ws.Cells[si + i, 1] = HttpUtility.HtmlDecode((i + 1).ToString());
                    ws.Cells[si + i, 2] = "'" + HttpUtility.HtmlDecode(GV.Rows[i].Cells[1].Text.ToString());
                    ws.Cells[si + i, 3] = HttpUtility.HtmlDecode(GV.Rows[i].Cells[2].Text.ToString());
                    ws.Cells[si + i, 4] = HttpUtility.HtmlDecode(GV.Rows[i].Cells[3].Text.ToString());
                    ws.Cells[si + i, 5] = HttpUtility.HtmlDecode(GV.Rows[i].Cells[4].Text.ToString());
                    ws.Cells[si + i, 6] = HttpUtility.HtmlDecode(GV.Rows[i].Cells[5].Text.ToString());
                    ws.Cells[si + i, 7] = "'" + HttpUtility.HtmlDecode(GV.Rows[i].Cells[6].Text.ToString());
                    ws.Cells[si + i, 8] = HttpUtility.HtmlDecode(GV.Rows[i].Cells[7].Text.ToString());
                    #endregion
                }

                Border = ws.get_Range("A" + (stt + 7).ToString(), "H" + (stt + 7).ToString());
                Border.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
                Microsoft.Office.Interop.Excel.Range e2 = ws.get_Range("C" + (stt + 7).ToString(), "C" + (stt + 7).ToString());
                e2.Formula = "Tổng cộng";
                e2.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                e2.Font.Bold = true;

                string[] footer = { "D", "E" };

                for (int k = 0; k <= 1; k++)
                {
                    Microsoft.Office.Interop.Excel.Range h = ws.get_Range(footer[k] + (stt + 7).ToString(), footer[k] + (stt + 7).ToString());
                    h.Formula = "=SUM(" + footer[k] + "7:" + footer[k] + (6 + stt).ToString() + ")";
                    h.Font.Bold = true;
                }

                #endregion


                string path = Server.MapPath("tmp.xls");
                FileInfo f = new FileInfo(path);
                if (f.Exists) f.Delete();
                wb.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, missvalues, missvalues, missvalues, missvalues, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missvalues, missvalues, missvalues, missvalues, missvalues);

                wb.Close(true, missvalues, missvalues);
                exc.Quit();
                releaseObject(ws);
                releaseObject(wb);
                releaseObject(exc);
                if (processID != 0)
                {
                    Process process = Process.GetProcessById(processID);
                    process.Kill();
                }

                string tk = dlnhacungcap.SelectedIndex > 0 ? dlnhacungcap.SelectedItem.Text : "TongHop";
                string filename = "DSTTCN-" + tk + "-" + thang;
                HttpResponse response = HttpContext.Current.Response;
                response.ClearContent();
                response.ContentType = "application/vnd.ms-excel"; ;
                response.AddHeader("Content-Disposition", "Attachment; filename=" + filename + ".xls;");

                MemoryStream stream;
                FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
                byte[] img = new byte[fs.Length];
                fs.Read(img, 0, Convert.ToInt32(fs.Length));
                fs.Close();
                fs.Dispose();
                stream = new MemoryStream(img);
                stream.WriteTo(Response.OutputStream);

                if (f.Exists) f.Delete();
                response.Flush();
                response.End();
            }

        }
        catch (Exception ax)
        {
            gstGetMess(ax.Message, "");
        }
    }
    static public void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            throw new Exception("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }
    protected void Button5_Click(object sender, EventArgs e)
    {
        try
        {
            if (GVPhieuChi.Rows.Count > 0)
            {
                Process[] processBefore = Process.GetProcessesByName("excel");
                Microsoft.Office.Interop.Excel.ApplicationClass exc = new Microsoft.Office.Interop.Excel.ApplicationClass();
                object missvalues = System.Reflection.Missing.Value;
                string checkpath = Server.MapPath("Excel/DSPhieuChiNganHang.xls");
                Microsoft.Office.Interop.Excel.Workbook wb = exc.Workbooks.Open(checkpath, 0, true, 3, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                exc.Visible = false;
                exc.StandardFont = "Times New Roman";
                exc.StandardFontSize = 13;

                string bcc = dlnhacungcap.SelectedIndex.Equals(0) ? "" : dlnhacungcap.SelectedItem.Text;
                string thang = drthang.SelectedItem.Text + " " + drnam.SelectedItem.Text;

                Process[] processAffre = Process.GetProcessesByName("excel");
                int processID = 0;
                foreach (Process pro in processAffre)
                {
                    if (!processBefore.Select(p => p.Id).Contains(pro.Id))
                        processID = pro.Id;
                }


                int stt = GVPhieuChi.Rows.Count;
                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[1];

                ws.Name = dlnhacungcap.SelectedIndex > 0 ? dlnhacungcap.SelectedItem.Text : "Bảng tổng hợp";
                Microsoft.Office.Interop.Excel.Range Border;

                #region TongHop
                string tieudethang1 = drthang.SelectedItem.Text.ToUpper() + " " + drnam.SelectedItem.Text.ToUpper();

                ws.get_Range("A3", "H3").Merge(true);
                Microsoft.Office.Interop.Excel.Range a4 = ws.get_Range("A3", "H3");
                a4.FormulaR1C1 = "DANH SÁCH PHIẾU CHI NGÂN HÀNG " + tieudethang1;
                a4.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                a4.Font.Size = 18;
                a4.EntireColumn.RowHeight = 23;
                a4.Font.Name = "Times New Roman";
                a4.Font.Bold = true;

                ws.get_Range("A4", "H4").Merge(true);
                Microsoft.Office.Interop.Excel.Range a4m42 = ws.get_Range("A4", "H4");
                a4m42.FormulaR1C1 = dlnhacungcap.SelectedIndex > 0 ? "Bên nhận tiền: " + dlnhacungcap.SelectedItem.Text : "";
                a4m42.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                a4m42.Font.Size = 14;
                a4m42.EntireColumn.RowHeight = 23;
                a4m42.Font.Name = "Times New Roman";
                a4m42.Font.Bold = true;

                int si = 6;
                for (int i = 0; i < stt; i++)
                {
                    Border = ws.get_Range("A" + (si), "H" + (si + i));
                    Border.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
                    #region Cells
                    ws.Cells[si + i, 1] = HttpUtility.HtmlDecode((i + 1).ToString());
                    ws.Cells[si + i, 2] = "'" + HttpUtility.HtmlDecode(GVPhieuChi.Rows[i].Cells[0].Text.ToString());
                    ws.Cells[si + i, 3] = HttpUtility.HtmlDecode(GVPhieuChi.Rows[i].Cells[1].Text.ToString());
                    ws.Cells[si + i, 4] = HttpUtility.HtmlDecode(GVPhieuChi.Rows[i].Cells[2].Text.ToString());
                    ws.Cells[si + i, 5] = HttpUtility.HtmlDecode(GVPhieuChi.Rows[i].Cells[3].Text.ToString());
                    ws.Cells[si + i, 6] = HttpUtility.HtmlDecode(GVPhieuChi.Rows[i].Cells[4].Text.ToString());
                    ws.Cells[si + i, 7] = HttpUtility.HtmlDecode(GVPhieuChi.Rows[i].Cells[5].Text.ToString());
                    ws.Cells[si + i, 8] = HttpUtility.HtmlDecode(GVPhieuChi.Rows[i].Cells[6].Text.ToString());
                    #endregion
                }

                Border = ws.get_Range("A" + (stt + 6).ToString(), "H" + (stt + 6).ToString());
                Border.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
                Microsoft.Office.Interop.Excel.Range e2 = ws.get_Range("C" + (stt + 6).ToString(), "C" + (stt + 6).ToString());
                e2.Formula = "Tổng cộng";
                e2.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                e2.Font.Bold = true;

                string[] footer = { "E" };

                for (int k = 0; k <= 0; k++)
                {
                    Microsoft.Office.Interop.Excel.Range h = ws.get_Range(footer[k] + (stt + 6).ToString(), footer[k] + (stt + 6).ToString());
                    h.Formula = "=SUM(" + footer[k] + "6:" + footer[k] + (5 + stt).ToString() + ")";
                    h.Font.Bold = true;
                }

                #endregion


                string path = Server.MapPath("tmp.xls");
                FileInfo f = new FileInfo(path);
                if (f.Exists) f.Delete();
                wb.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, missvalues, missvalues, missvalues, missvalues, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, missvalues, missvalues, missvalues, missvalues, missvalues);

                wb.Close(true, missvalues, missvalues);
                exc.Quit();
                releaseObject(ws);
                releaseObject(wb);
                releaseObject(exc);
                if (processID != 0)
                {
                    Process process = Process.GetProcessById(processID);
                    process.Kill();
                }

                string tk = dlnhacungcap.SelectedIndex > 0 ? dlnhacungcap.SelectedItem.Text : "TongHop";
                string filename = "DSPCNH-" + tk + "-" + thang;
                HttpResponse response = HttpContext.Current.Response;
                response.ClearContent();
                response.ContentType = "application/vnd.ms-excel"; ;
                response.AddHeader("Content-Disposition", "Attachment; filename=" + filename + ".xls;");

                MemoryStream stream;
                FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
                byte[] img = new byte[fs.Length];
                fs.Read(img, 0, Convert.ToInt32(fs.Length));
                fs.Close();
                fs.Dispose();
                stream = new MemoryStream(img);
                stream.WriteTo(Response.OutputStream);

                if (f.Exists) f.Delete();
                response.Flush();
                response.End();
            }

        }
        catch (Exception ax)
        {
            gstGetMess(ax.Message, "");
        }
    }
    protected void drthang_SelectedIndexChanged(object sender, EventArgs e)
    {
        p = new SqlParameter[2];
        p[0] = new SqlParameter("@Thang", int.Parse(drthang.SelectedValue));
        p[0].SqlDbType = SqlDbType.Int;
        p[1] = new SqlParameter("@Nam", int.Parse(drnam.SelectedValue));
        p[1].SqlDbType = SqlDbType.Int;
        dlnhacungcap.Items.Clear();
        dlnhacungcap.DataSource = ex.GetData("sp_ThanhToanCN_NhaCungCap_TK_TheoThoiGian", p);
        dlnhacungcap.DataBind();
        dlnhacungcap.Items.Insert(0, new ListItem("--Chọn bên thanh toán--", ""));
        dlnhacungcap.SelectedIndex = 0;
    }
}